package com.FindMyStuff.Model;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Helper for the database
 * Contains all the database structure information
 * include table names and column names
 *
 */
public class DataBaseHelper extends SQLiteOpenHelper {

	public DataBaseHelper(Context context) {
		super(context, databaseName, null, databaseVersion);
	}

	private static final int databaseVersion = 5;	
	public static final String databaseName = "Where's My Stuff BRAH";
	
	public static final String userTable = "user";
	public static final String userColumnPassword = "password";
	public static final String userColumnEmail = "email";
	public static final String userColumnStatus = "status";
	public static final String userColumnAttempts = "attempts_left";
	public static final String userColumnRole = "role";
	public static final String userAllColumns[] = new String[] {userColumnEmail, userColumnPassword,
					userColumnRole, userColumnStatus, userColumnAttempts};
	
	private static final String USER_CREATE = "create table " +userTable + "("
					+ userColumnEmail + " text not null primary key, "
					+ userColumnPassword + " text not null, " 
					+ userColumnRole + " text not null, "
					+ userColumnStatus + " text not null, "
					+ userColumnAttempts + " integer not null)";
	
	public static final String itemTable = "item";
	public static final String itemID = "id";
	public static final String itemName = "name";
	public static final String itemDescription = "description";
	public static final String itemLocation = "location";
	public static final String itemStatus = "status";
	public static final String itemCategory = "category";
	public static final String itemReward = "reward";
	public static final String itemType = "type";
	public static final String itemDate = "date";
	public static final String itemOwner = "owner";
	public static final String itemAllColumns[] = new String[] {itemID, itemName, itemDescription, itemLocation,
					itemStatus, itemCategory, itemReward, itemType, itemDate, itemOwner};
	
	public static final String allDBTables[] = new String[] {userTable, itemTable};
	
	private static final String ITEM_CREATE = "create table " + itemTable + "("
					+ itemID + " integer primary key autoincrement, " 
					+ itemName + " text not null, " 
					+ itemDescription + " text not null, " 
					+ itemLocation + " text not null, " 
					+ itemStatus + " text not null, " 
					+ itemCategory + " text not null, " 
					+ itemReward + " text, " 
					+ itemType + " text not null, " 
					+ itemDate + " text not null, " 
					+ itemOwner + " text not null," 
					+ " FOREIGN KEY ("+itemOwner+") REFERENCES "+userTable+" ("+userColumnEmail+"));";
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(USER_CREATE);
		db.execSQL(ITEM_CREATE);
		db.execSQL("insert into user values('admin', 'admin', 'a', 'u', 3)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("drop table if exists " + userTable);
		db.execSQL("drop table if exists " + itemTable);
		onCreate(db);
	}
}
